package com.cib.ccc.poc.data;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.math.BigDecimal;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.eventusermodel.examples.XLS2CSVmra;

import com.cib.ccc.poc.data.bean.AnalysisObject;
import com.cib.ccc.poc.data.dao.AnalysisObjectDAO;

/**
 * TODO 比较Oralce sqlldr方法导入数据
 * @author ccc-yangyiming
 *
 */
public class BulkProcess {
	
	public final static String MODE_CSV_SQLLDR="CSV";
	public final static String MODE_BULK_SQL="SQL";
	
	
	private String spreadsheet = "d:/temp/";
	
	public BulkProcess(String spreadsheet)
	{
		this.spreadsheet = spreadsheet;
	}
	
	public void convertToCsv() throws Exception
	{			
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(this.spreadsheet));
		FileOutputStream csvFos =new FileOutputStream("d:/temp/result.csv");
		PrintStream output = new PrintStream(csvFos,false,"GBK");
		int minColumns = -1;
		
		XLS2CSVmra converter = new XLS2CSVmra(fs,output,minColumns);
		
		converter.process();
	}
	
	private BigDecimal toBigDecimal(String s)
	{
		if(s!= null && s.length()>0)
		{
			return BigDecimal.valueOf(Double.parseDouble(s));
		}else
		{
			return new BigDecimal(0);
		}
	}
	
	private String getCellString(HSSFCell cell)
	{
		if(cell == null)
		{
			//如果cell中没内容，就可能是null
			return "";
		}
		
		if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
		{
			if(HSSFDateUtil.isCellDateFormatted(cell))
			{
				double d=cell.getNumericCellValue();
				java.util.Date date = HSSFDateUtil.getJavaDate(d);
				SimpleDateFormat sFormat=new SimpleDateFormat("yyyy-MM-dd");				
				return sFormat.format(date);
			}else
			{
				return cell.getNumericCellValue()+"";
			}			
		}
		if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
		{
			return cell.getStringCellValue();
		}
		if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
		{
			return cell.getBooleanCellValue()+"";
		}
		return "";
	}
	
	private AnalysisObject toObject(HSSFRow row)
	{
		AnalysisObject obj = new AnalysisObject();
		// 起始日期	
		HSSFCell cell = row.getCell(0);
		
		//判断空白行
		if(cell == null)
		{
			return null;
		}
		
		String startDate = getCellString(cell);
		if(startDate != null && startDate.length()>0)
		{
			obj.setStartDate(Date.valueOf(startDate));
		}
		
		//终止日期	
		cell = row.getCell(1);
		String endDate = getCellString(cell);
		if(endDate != null && endDate.length()>0)
		{
			obj.setEndDate(Date.valueOf(endDate));
		}
		
		//账龄	
		cell = row.getCell(2);
		String cardAge = getCellString(cell);
		obj.setCardAge(cardAge);
		
		//卡种	
		cell = row.getCell(3);
		String cardType = getCellString(cell);
		obj.setCardType(cardType);
		
		//近6个月客户价值	
		cell = row.getCell(4);
		String value = getCellString(cell);
		obj.setValue_6m(value);
		
		//分行	
		cell = row.getCell(5);
		String branch = getCellString(cell);
		obj.setBranch(branch);
		
		//POS消费金额	
		cell = row.getCell(6);
		String posAmt = getCellString(cell);
		obj.setPosAmt(toBigDecimal(posAmt));
				
		//批发类交易量	
		cell = row.getCell(7);
		String transNum1 = getCellString(cell);
		obj.setTransNum_Wholesale(toBigDecimal(transNum1));
		
		//新兴行业类交易量	
		cell = row.getCell(8);
		String transNum2 = getCellString(cell);
		obj.setTransNum_NewIndustry(toBigDecimal(transNum2));
		
		//县乡优惠类	
		cell = row.getCell(9);
		String transNum3 = getCellString(cell);
		obj.setTransNum_CountrysidePromotion(toBigDecimal(transNum3));
		
		//房产类交易量	
		cell = row.getCell(10);
		String transNum4 = getCellString(cell);
		obj.setTransNum_RealEstate(toBigDecimal(transNum4));
		
		//汽车销售类交易量	
		cell = row.getCell(11);
		String transNum5 = getCellString(cell);
		obj.setTransNum_Car(toBigDecimal(transNum5));
		
		//公益类交易量	
		cell = row.getCell(12);
		String transNum6 = getCellString(cell);
		obj.setTransNum_Commonweal(toBigDecimal(transNum6));
		
		//大宗分期交易量
		cell = row.getCell(13);
		String transNum7 = getCellString(cell);
		obj.setTransNum_BulkInstallments(toBigDecimal(transNum7));
		
		return obj;
	}
	
	public void sqlInsert() throws FileNotFoundException, IOException
	{
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(this.spreadsheet));
		HSSFWorkbook work = new HSSFWorkbook(fs);
		HSSFSheet sheet = work.getSheetAt(0);
		int row = sheet.getLastRowNum();
		
		List<AnalysisObject> objects = new ArrayList();
		
		//第一行不读
		for (int rowIndex = 1; rowIndex <= row; rowIndex++) {
			System.out.println(rowIndex);
			HSSFRow rowObj = sheet.getRow(rowIndex);
			AnalysisObject obj = this.toObject(rowObj);
			if(obj == null)
			{
				break;
			}
			objects.add(obj);
		}
		
		AnalysisObjectDAO dao = new AnalysisObjectDAO();
		dao.buldInsert(objects);
	}
	
	public void run(String type) throws Exception
	{
		 Long startTime = System.currentTimeMillis();

		 if(type.equals(BulkProcess.MODE_BULK_SQL))
		 {
			 this.sqlInsert();
		 }else if(type.equals(MODE_CSV_SQLLDR))
		 {
			 this.convertToCsv();
		 }
		 
		 Long endTime = System.currentTimeMillis();
		 System.out.println("用时：" + (endTime - startTime));
	}
	
	public static void main(String[] args) throws Exception
	{
		BulkProcess p = new BulkProcess("d:/temp/20120301-20120307周报1.xls");
		p.run(MODE_BULK_SQL);
	}
}
